<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
*/

/**
 * Description of DataSet
 *
 * @author Guilherme S2 Karin
 */
require_once 'Database.php';
require_once 'ArrayListField.php';
require_once 'Field.php';
require_once 'funcoes.php';
require_once 'fieldType.php';
require_once 'Link.php';

class DataSet extends dbase {
    private $eof, $chaves,$db,$result,$cChaves,$table,$links,$linkCount,$condCount;
    public $status,$fields,$sql;

    function DataSet() {
        $this->db = $this->dbase();
        $this->fields = new ArrayList();
        $sql = '';
        $result = '';
        $status = 'Brouse';
        $this->condCount =0;
        $this->linkCount = 0;
    }

    function paramByName($paramName, $value) {
        $this->sql = str_replace(':'.$paramName, $value, $this->sql);
    }

    function getCodSeq($generator) {

        $res = $this->exec_sql("select gen_id($generator,1) from rdb".'$database');
        $cod = ibase_fetch_assoc($res);
        return $cod['GEN_ID'];
    }

    function sqlCreate($table) {
        $this->sql ='';
        $this->sql = "Select $table.* ";
        $aux = '';
        if ($this->linkCount > 0) {
            for($i = 0;$i<count($this->links);$i++) {
                $link = $this->links[$i];
                for ($j = 0;$j<$link->fieldCount();$j++) {
                    $this->sqlAdd(",".$link->getTable().'.'.$link->getField($j));
                }

            }
        }
    
        $this->sqlAdd(" from $table ");

        if ($this->linkCount > 0) {
            for($i = 0;$i<count($this->links);$i++) {
                $link = $this->links[$i];
                $this->sqlAdd(' inner join '.$link->getTable().' on ');
                $this->sqlAdd($link->getTable().'.'.$link->camposChaveO[0].' = '.$this->Table().'.'.$link->camposChaveD[0]);
                for ($j = 1;$j<count($link->camposChaveO);$j++) {
                    $this->sqlAdd(" and ".($link->getTable().'.'.$link->camposChaveO[$j].' = '.$this->Table().'.'.$link->camposChaveD[$j]));
                }

            }
        }
       

    }

    function setTable($table) {
        $this->table = $table;
    }

    function sqlClear() {
        $this->sql = '';
        $this->condCount = 0;
    }

    function sqlAdd($value) {
        $this->sql .= $value;

    }

    function addCondition($value) {
        if ($this->condCount > 0)
            $this->sql .= ' and '.$value;
        else
            $this->sql .= ' where '.$value;
        $this->condCount++;
    }

    function typeField($table,$field) {
        try {
            $sql = "SELECT
                    distinct
                    RRF.RDB\$FIELD_NAME AS CAMPO,
                                CASE
                                  RTP.RDB\$TYPE_NAME
                                    WHEN 'VARYING'  THEN  'VARCHAR'
                                    WHEN 'LONG'     THEN  'INTEGER'
                                    WHEN 'SHORT'    THEN  'SMALLINT'
                                    WHEN 'DOUBLE'   THEN  'DOUBLE PRECISION'
                                    WHEN 'FLOAT'    THEN  'DOUBLE PRECISION'
                                    WHEN 'INT64'    THEN  'NUMERIC'
                                    WHEN 'TEXT'     THEN  'CHAR'
                                    ELSE RTP.RDB\$TYPE_NAME
                                END TIPO_CAMPO,
                               -- RTP.RDB\$TYPE_NAME,
                                CASE
                                  RTP.RDB\$TYPE_NAME
                                    WHEN  'VARYING' THEN RFL.RDB\$FIELD_LENGTH
                                    ELSE  RFL.RDB\$FIELD_PRECISION
                                END AS TAMANHO,
                                (RFL.RDB\$FIELD_SCALE * -1) AS ESCALA,
                                IIF(  EXISTS(   SELECT      FIRST 1 1
                                                FROM        RDB\$RELATION_CONSTRAINTS  RCN
                                                INNER JOIN  RDB\$CHECK_CONSTRAINTS     CCN     ON    RCN.RDB\$CONSTRAINT_NAME = CCN.RDB\$CONSTRAINT_NAME AND
                                                                                                    CCN.RDB\$TRIGGER_NAME = RRF.RDB\$FIELD_NAME
                                                 WHERE      RCN.RDB\$RELATION_NAME =  RRF.RDB\$RELATION_NAME ),
                                      1,
                                      0)  AS  NOT_NULL
                    FROM        RDB\$RELATION_FIELDS   RRF
                    INNER JOIN  RDB\$FIELDS            RFL     ON    RFL.RDB\$FIELD_NAME = RRF.RDB\$FIELD_SOURCE
                    INNER JOIN  RDB\$TYPES             RTP     ON    RTP.RDB\$TYPE = RFL.RDB\$FIELD_TYPE AND
                                                                    RTP.RDB\$FIELD_NAME = 'RDB\$FIELD_TYPE'
                    LEFT JOIN   RDB\$INDEX_SEGMENTS    RIS     ON    RIS.RDB\$FIELD_NAME = RRF.RDB\$FIELD_NAME AND
                                                                    EXISTS (  SELECT      FIRST 1 1
                                                                              FROM        RDB\$INDICES   IND
                                                                              INNER JOIN  RDB\$REF_CONSTRAINTS   RFC   ON    RFC.RDB\$CONSTRAINT_NAME = IND.RDB\$INDEX_NAME
                                                                              WHERE       IND.RDB\$INDEX_NAME = RIS.RDB\$INDEX_NAME AND
                                                                                          IND.RDB\$RELATION_NAME = RRF.RDB\$RELATION_NAME)
                    LEFT JOIN   RDB\$REF_CONSTRAINTS   RFC     ON    RFC.RDB\$CONSTRAINT_NAME = RIS.RDB\$INDEX_NAME
                    LEFT JOIN   RDB\$INDEX_SEGMENTS    RIS2    ON    RIS2.RDB\$INDEX_NAME = RFC.RDB\$CONST_NAME_UQ AND
                                                                    RIS2.RDB\$FIELD_POSITION = RIS.RDB\$FIELD_POSITION
                    LEFT  JOIN  RDB\$RELATION_CONSTRAINTS RRC  ON    RFC.RDB\$CONST_NAME_UQ = RRC.RDB\$CONSTRAINT_NAME AND
                                                                    RRC.RDB\$CONSTRAINT_TYPE = 'PRIMARY KEY'
                    WHERE       RRF.RDB\$RELATION_NAME NOT STARTING WITH 'RDB$'
                    and
                      RRF.RDB\$RELATION_NAME = '$table'
                    and
                      RRF.RDB\$FIELD_NAME = '$field'
                    ORDER BY    RRF.RDB\$RELATION_NAME ";
            $query = $this->exec_sql($sql);
            $result = ibase_fetch_object($query);
            $campo = new fieldType();
            $campo->type = trim($result->TIPO_CAMPO);
            $campo->size = $result->TAMANHO;
            $campo->scale = $result->ESCALA;
            $campo->NotNull = ($result->NOT_NULL == 1);
            return $campo;
        }catch (Exception $e) {
            gravaLogDeErros($e->getMessage());
        }
    }

    /***
     * funcao prepada dataset para as operações *
     */
    function Prepare() {
        gravaLogDeSQL($this->sql);
        $sqlFields = 'SELECT
                trim(FIELDS.RDB$FIELD_NAME)
       FROM
                RDB$RELATION_FIELDS FIELDS
       WHERE
                (upper(FIELDS.RDB$RELATION_NAME) = upper('."'".$this->Table()."'".'))
       ORDER BY
                FIELDS.RDB$FIELD_POSITION';
        gravaLogDeSQL($sqlFields);
        try {
            $this->result = ibase_query($this->sql);
            $res = ibase_query($sqlFields);
            if ($res) {
                $aux = ibase_fetch_assoc($this->result);
                while ($aux2 = ibase_fetch_row($res)) {
                    if ($aux)
                        $field = new Field($aux2[0],$aux[$aux2[0]]);
                    else
                        $field = new Field($aux2[0],"");
                    gravaLogDeFluxo($field->nome.' '.$field->valor);
                    $this->fields->Add($field);
                }
            }
            //cria os fields links
            for ($i =0;$i < $this->linkCount;$i++) {
                $link = $this->links[$i];
                for ($j = 0;$j < count($link->campos);$j++){
                    $field = new Field($link->getField($j),$aux[$link->getField($j)]);
                    $this->fields->add($field);
                }

            }

            for ($i=0;$i<$this->fieldCount();$i++){
            //    echo $this->fields($i)->getNome().'<br>';
            }
           
        }catch (Exception $e) {
            gravaLogDeErros('SQL Fields :'.$e->getMessage());
            $this->eof = true;
        }

    }

    public function link($tabela,$campos,$camposChaveO,$camposChaveD) {
        $this->links[$this->linkCount] = new Link($tabela,$campos,$camposChaveO,$camposChaveD);
        $this->linkCount++;
    }

    /**
     * Função retorna a tabela usada no sql
     */
    private function Table() {
        $pare = false;
        $aux = "";
        $entrou = false;
        for ($i = strpos(strtoupper($this->sql), 'FROM')+4;(($i < strlen($this->sql))&&(!$pare));$i++) {
            if ($this->sql[$i] != ' ') {
                $entrou = true;
                $aux .= $this->sql[$i];
            }else if ($entrou) {
                $pare = true;
            }
        }
        return $aux;
    }

    function isEof() {
        return $this->eof;
    }

    function fields($index) {
        return $this->fields->get($index);
    }

    /*   function recordCount(){
        return ibase_cos($this->result);
    }*/

    function  fieldByName($fieldName) {
        for ($i = 0;$i<$this->fields->count;$i++) {

            if ($this->fields->get($i)->getNome() == $fieldName) {
                return $this->fields->get($i);
            }
        }

        return null;
    }

    function next() {
        $aux = ibase_fetch_assoc($this->result);
        if ($aux) {
            $this->eof = false;


            for ($i = 0;$i < $this->fields->count;$i++) {
                $oField = $this->fields->get($i);
                ;
                $oField->setValor($aux[$oField->getNome()]);
                $this->fields->set($i,$oField);
            }
        }else {
            $this->eof = true;
            for ($i = 0;$i < $this->fields->count;$i++) {
                $oField = $this->fields->get($i);
                $oField->setValor('');
                $this->fields->set($i,$oField);
            }
        }
    }

    private function clearFields() {
        for ($i = 0;$i<$this->fields->count;$i++ ) {
            $oField = $this->fields->get($i);
            $oField->setValor('');
        }
    }

    function insert() {
        $this->clearFields();
        $this->status = 'Insert';
    }

    function post() {
        $result = false;
        if ($this->status == 'Insert') {
            if ($this->Inserir($this->Table(),$this->fields)) {
                $result = true;
            }else {
                $result = false;
            }
        }else if ($this->status == 'Edit') {
            $this->editar($this->cChaves);
        }else {

        }
        return $result;
    }



    private function query($sql) {
        return(ibase_query($sql,$this->db));
    }

    private function query_obj($sql) {
        $aux = ibase_query($sql,$this->db);
        return(ibase_fetch_object($aux));
    }

    public function exec_sql($sql) {
        $result = ibase_query($sql);
        return($result);
    }



    private function Update($tabela,$cond) {
        $sql = "Update $tabela  set ";
        for ($i=0;$i<$this->fields->count;$i++) {
            if ($i == (($this->fields->count)-1)) {
                $sql .= $this->fields->get($i)->getNome().' = '.($this->fields->get($i)->getValor());
            }else {
                $sql .= $this->fields->get($i)->getNome().' = '.$this->fields->get($i)->getValor().' ,';
            }
        }
        if ($cond != '') {
            $sql = $sql.' '.$cond;
        }
        gravaLogDeSQL($sql);
        ibase_query($sql,$this->dbase());
    }

    //Campos é um array onde a primeira linha são colocados os campos
    //E na segunda linha são colocados os valores
    private function Inserir($tabela,ArrayList $campos) {
        $colunas = $campos->count;
        $sql= "insert into $tabela (";
        for ($i = 0; $i <= 1; $i++) {
            for ($j =0; $j < $colunas; $j++) {
                if ($i == 0) {
                    if ($j+1 < $colunas ) {
                        $sql = $sql.$campos->get($j)->getNome().',';
                    }else {
                        $sql = $sql.$campos->get($j)->getNome();
                    }
                    if ($j+1 == $colunas) {
                        $sql = $sql.') VALUES (';
                    }
                }else {
                    if ($j+1 < $colunas ) {
                        // if (!is_integer($sql.$campos->get($j)->getValor()))
                        //    $sql = $sql."'".$campos->get($j)->getValor()."'".',';
                        // else
                        $sql = $sql.$campos->get($j)->getValor().',';
                    }else {
                        // if (($sql.$campos->get($j)->getValor()))
                        //               $sql = $sql."'".$campos->get($j)->getValor()."'";
                        //   else
                        $sql = $sql.$campos->get($j)->getValor();
                    }
                    if ($j+1 == $colunas) {
                        $sql = $sql.')';
                    }
                }
            }
        }
        gravaLog($sql, "a", "sqls.log");
        return($this->exec_sql($sql));
    }

    private function consulta($tabela,$campos,$condicao) {
        $sql = "Select ";
        if ($campos == '') {
            $sql.= "* from $tabela ";
        }else {
            $sql.= "$campos from $tabela ";
        }
        if ($condicao != '') {
            $sql .= ' where ';
            for ($i = 0;$i < count($condicao);$i++) {
                $sql.= $condicao[$i];
                if (count($condicao) > 1) {
                    $sql .= ' and ';
                }

            }
        }
        // echo $sql;
        $qry = $this->query($sql);
        for ($i=0;$result = ibase_fetch_row($qry);$i++) {
            $aux[$i] = $result;
        }

        return($aux);
    }

    function close() {
        mysql_close($this->db);
    }

    function fieldCount() {
        return $this->fields->count;
    }

    function edit(array $camposChave) {
        $this->cChaves = $camposChave;
        $this->status = 'Edit';
    }

    function editar($camposChave) {
        $cond = ' where ';
        for ($i = 0;$i<count($camposChave);$i++) {
            $cond .= ' '.$camposChave[$i].' = '.$this->fieldByName($camposChave[$i])->getValor();
        }
        $this->Update($this->Table(), $cond);
    }

    function getObject() {
        try {
            $aux = "dataset/map/".$this->Table().".xml";

            $DOMDocument = DOMDocument::load($aux);

            $item  = $DOMDocument->getElementsByTagName('object')->item(0);
            $aux = $item->getAttribute('id');
            require_once 'vo/'.$aux.'.php';
            $obj = new $aux;
            $i = 0;
            $item = $DOMDocument->getElementsByTagName('atribute')->item($i);

            while ($item != null) {

                $campo = $item->getAttribute('var');
                $obj->$campo = $this->fieldByName($item->getAttribute('field'))->getValor();
                $i++;
                $item = $DOMDocument->getElementsByTagName('atribute')->item($i);
            }
            return $obj;
        }catch (Exception $e) {
            gravaLogDeErros($e->getMessage());
        }
    }

    function getObjectArray() {
        $array = null;
        for ($i = 0;!$this->isEof();$i++) {
            $array[$i] = $this->getObject();
            $this->next();
        }

        return $array;
    }
}
?>
